﻿using KmSql.Shared.Attributes;
using KmSql.SqlServer.Ado;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace KmSql.SqlServer
{
    public class SqlServerProvider : IKmSql
    {
        public string ConnectionString { get; }
        public SqlServerProvider(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public IAdo Ado => new SqlServerAdo(ConnectionString);

        public Task<List<T>> ToListAsync<T>() where T : class
        {
            string sql = $"SELECT * FROM {GetTableName<T>()};";
            return Ado.QueryAsync<T>(sql);
        }

        public Task<T?> FindAsync<T>(object keyValue) where T : class
        {
            if (keyValue == null)
                return Task.FromResult(default(T));
            string keyName = GetKeyName<T>();
            string sql = $"SELECT TOP 1 * FROM {GetTableName<T>()} WHERE {keyName}=@{keyName};";
            return Ado.FirstAsync<T>(sql, new SqlParameter[] { new SqlParameter($"@{keyName}", keyValue) });
        }

        public Task<int> AddAsync<T>(T entity) where T : class
        {
            if (entity == null)
                return Task.FromResult(0);
            string nameSql = string.Empty;
            string valueSql = string.Empty;
            List<SqlParameter> pars = new List<SqlParameter>();
            var properties = typeof(T).GetProperties();
            if (properties.Count() == 0)
                return Task.FromResult(0);
            foreach (var property in properties)
            {
                ColumAttribute? colum = property.GetCustomAttribute(typeof(ColumAttribute)) as ColumAttribute;
                if (colum?.IsIdentity ?? false)
                    continue;
                string name = colum?.Name ?? property.Name;
                nameSql += property.Name + ",";
                valueSql += $"@{property.Name},";
                pars.Add(new SqlParameter(property.Name, property.GetValue(entity)));
            }
            if (pars.Count == 0)
                return Task.FromResult(0);
            string sql = $"INSERT INTO {GetTableName<T>()}({nameSql.TrimEnd(',')}) VALUES({valueSql.TrimEnd(',')});";
            return Ado.ExecuteNonQueryAsync(sql, pars.ToArray());
        }

        public Task<int> UpdateAsync<T>(T entity) where T : class
        {
            if (entity == null)
                return Task.FromResult(0);
            var properties = typeof(T).GetProperties();
            if (properties.Count() == 0)
                return Task.FromResult(0);
            string updateSql = string.Empty;
            string whereSql = string.Empty;
            List<SqlParameter> pars = new List<SqlParameter>();
            foreach (var property in properties)
            {
                ColumAttribute? colum = property.GetCustomAttribute(typeof(ColumAttribute)) as ColumAttribute;
                string name = colum?.Name ?? property.Name;
                if (colum?.IsPrimaryKey ?? false)
                    whereSql += $"{name}=@{name},";
                else
                    updateSql += $"{name}=@{name},";
                pars.Add(new SqlParameter(name, property.GetValue(entity)));
            }
            string sql = $"UPDATE {GetTableName<T>()} set {updateSql.TrimEnd(',')} WHERE {whereSql.TrimEnd(',')};";
            return Ado.ExecuteNonQueryAsync(sql, pars.ToArray());
        }

        public Task<int> DeleteAsync<T>(object keyValue) where T : class
        {
            var keyName = GetKeyName<T>();
            string sql = $"DELETE FROM {GetTableName<T>()} WHERE {keyName}=@{keyName};";
            return Ado.ExecuteNonQueryAsync(sql, new SqlParameter[] { new SqlParameter($"@{keyName}", keyValue) });
        }

        private string GetTableName<T>() where T : class
            => typeof(T).GetCustomAttribute<TableAttribute>()?.Name ?? typeof(T).Name;

        private string GetKeyName<T>() where T : class
        {
            var keyProperty = typeof(T).GetProperties().Where(x =>
            x.GetCustomAttribute<ColumAttribute>()?.IsPrimaryKey ?? false).FirstOrDefault();
            if (keyProperty == null)
                throw new Exception($"【KmSql】：The Table '{nameof(T)}' has not a Key. ");
            return keyProperty.GetCustomAttribute<ColumAttribute>()!.Name ?? keyProperty?.Name ?? "";

        }

        private (string keysWhereSql, SqlParameter[] keysPars) GetKeysInfo<T>(T entity) where T : class
        {
            string keysWhereSql = string.Empty;
            List<SqlParameter> keysPars = new List<SqlParameter>();
            foreach (var property in typeof(T).GetProperties())
            {
                ColumAttribute? colum = property.GetCustomAttribute(typeof(ColumAttribute)) as ColumAttribute;
                if (colum == null || !colum.IsPrimaryKey)
                    continue;
                string name = colum?.Name ?? property.Name;
                keysWhereSql += $"{name}=@{name},";
                keysPars.Add(new SqlParameter(name, property.GetValue(entity)));
            }
            if (keysPars.Count() == 0)
                throw new Exception($"【KmSql】：The Table '{nameof(T)}' has not a Key. ");
            return (keysWhereSql, keysPars.ToArray());
        }
    }
}
